<?php
include_once "db.php";
include_once "checksession.php"; 
?>

<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
    <title>
      Online College Helpdesk - Statistics
    </title>
    <link href="meta-style.css" rel="stylesheet" type="text/css" />
	
	<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js" type="text/javascript"></script>
	<script src="js/highcharts.js" type="text/javascript"></script>
	<script type="text/javascript">
		function sortTableByTotal(tableId, sorter){
			var table = document.getElementById(tableId);
			var tableBody = table.getElementsByTagName("tbody")[0];
			var rowNodes = tableBody.getElementsByTagName("tr");
			var rows = [];		//array of nodes - different from a NodeList!
			for(var i = 0; i < rowNodes.length; i++){
				rows[i] = rowNodes[i];
			}
			
			//sort the rows
			rows.sort(sorter);
			
			//remove the unsorted table rows
			while(table.rows > 0){
				table.deleteRow(0);		//the firstchild of <table> is <tbody>, whose children are <tr>
			}
			//table.appendChild(document.createElement("tbody"));

			for(var i = 0; i < rows.length; i++){
				tableBody.appendChild(rows[i]);
			}
        }
		
		function descending(a, b){
			//b - a
			return parseInt(b.getElementsByClassName("total")[0].innerHTML) - parseInt(a.getElementsByClassName("total")[0].innerHTML);
		}

		function ascending(a, b){
			//a - b
			return parseInt(a.getElementsByClassName("total")[0].innerHTML) - parseInt(b.getElementsByClassName("total")[0].innerHTML);
		}
	</script>
</head>
<body>
<?php
	include_once "topnav.php";
	
	if(empty($_GET))
	{
		//no date specified, defaults to 2011-01-01 till today
		//mysql date format is YYYY-MM-DD
		$startYear = 2011;
		$startMonth = 1;
		$startDay = 1;

		$endYear = date("Y");
		$endMonth = date("n");
		$endDay = date("j");
	}
	else 
	{
		$startYear = $_GET["sy"];
		$startMonth = $_GET["sm"];
		$startDay = $_GET["sd"];

		$endYear = $_GET["ey"];
		$endMonth = $_GET["em"];
		$endDay = $_GET["ed"];
	}
	$start = "$startYear-$startMonth-$startDay 00:00:00";
	$end = "$endYear-$endMonth-$endDay 23:59:59";

	
?>	
<div align="left" style="font-family:Verdana, Arial, Helvetica, sans-serif; font-size:10px;">
	<form id="date" method="get" action="newstats.php">
		From 00:00 on
		<select id="startMonth" name="sm">
			<?php
			for ($i = 1; $i <= 12; $i++)		//so months are 1-based index like the numerical date
			{
				echo "<option value=\"$i\" ";
				if ($i == $startMonth)
					echo "selected ";			//displays the query dates in the selecttion
				echo ">";
				echo $i;
				echo "</option>";
			}
			?>
		</select>
		<select id="startDay" name="sd">
			<?php		
			for ($i = 1; $i <= 31; $i++)		//every month will have 31 days. crazy roman calendar...
			{
				echo "<option value=\"$i\" "; 
				if ($i == $startDay)
					echo "selected ";
				echo ">";
				echo $i;
				echo "</option>";
			}
			?>
		</select>
		<select id="startYear" name="sy">
			<?php
			for ($i = 2007; $i <= date("Y"); $i++)	//it will automagically update for 2012 and 2013 etc. (if the world doesnt end)
			{
				echo "<option value=\"$i\" ";
				if ($i == $startYear)
					echo "selected ";
				echo ">";
				echo $i;
				echo "</option>";
			}
			?>
		</select>
		to 23:59 on
		<select id="endMonth" name="em">
			<?php
			for ($i = 1; $i <= 12; $i++)		//so months are 1-based index like the numerical date
			{
				echo "<option value=\"$i\" ";
				if ($i == $endMonth)
					echo "selected ";			//displays the query dates in the selecttion
				echo ">";
				echo $i;
				echo "</option>";
			}
			?>
		</select>
		<select id="endDay" name="ed">";
			<?php
			for ($i = 1; $i <= 31; $i++)		//every month will have 31 days. crazy roman calendar...
			{
				echo "<option value=\"$i\" "; 
				if ($i == $endDay)
					echo "selected ";
				echo ">";
				echo $i;
				echo "</option>";
			}
			?>
		</select>
		<select id="endYear" name="ey">
			<?php
			for ($i = 2007; $i <= date("Y"); $i++)
			{
				echo "<option value=\"$i\" ";
				if ($i == $endYear)
					echo "selected ";
				echo ">";
				echo $i;
				echo "</option>";
			}
			?>
		</select>
		<input type="submit" name="Submit" value="Submit" />
	</form>
</div>
<table>
	<tr>
		<td>
<div align="center">
    <table border="1" cellpadding="2" cellspacing="0" bordercolor="black" id="callResults">
		<thead>
			<tr class="title">
				<td>Problem Type:</td>
				<td>Total:</td>
				<td>% of total tickets:</td>
			</tr>
		</thead>
		<tbody>
			<?php
			//total calls
			$result = mysql_query("select count(*) from Calls WHERE Calls.request_date > '$start' AND Calls.request_date < '$end'");
			$totalTickets = mysql_result($result, 0);
			//problem types
			$problem_typeResult = mysql_query("select problem_type.PT_ID, problem_type.PT_DESC from problem_type");
			
			//bulid the rows of the table where the stats will be displayed
			//this code is messy, but looking at the resulting html should clarify it
			while ($problemType = mysql_fetch_array($problem_typeResult, MYSQL_ASSOC))
			{	
			?>
				<tr id="<?php echo $problemType["PT_ID"]; ?>">
					<td width="50%" class="description" name="problemDesc">
						<?php
						echo $problemType["PT_DESC"];
						?>
					</td>
					<td class="total">
						<?php
						$result = mysql_query("select count(*) from Calls WHERE problem_type='{$problemType["PT_ID"]}' AND Calls.request_date > '$start' AND Calls.request_date < '$end'");
						$total = mysql_result($result, 0);
						echo $total; 
						?>
					</td>
					<td class="percent">
						<?php
						//the @ in front of all the percentage calculations supresses divide by zero errors, and returns 0 instead
						$percent = round(@($total / $totalTickets) * 100, 1);
						echo $percent;
						echo "%";
						?>
					</td>
				</tr>
			<?php
			}	//end while loop
			?>
		</tbody>
		<tfoot>
			<tr>
				<td>
					<strong>Total Tickets:</strong>
				</td>
				<td id="totalTickets">
					<?php echo $totalTickets; ?>
				</td>
				<td>
					100%
				</td>
			</tr>
		</tfoot>
	</table>
<br/>
	<table border="1" cellpadding="2" cellspacing="0" bordercolor="black" id="transferResults">
		<thead>
			<tr class="title">
				<td>Referred to:</td>
				<td>Total:</td>
				<td>% of referral/transfers:</td>
				<td>% of tickets:</td>
			</tr>
		</thead>
		<tbody>
			<?php
			//Stats for call transfers - these arent in a database, so must be hard coded
			$transfers = array();		//I used an array so you can loop through it.
			$query = mysql_query("select count(*) from resolution_notes WHERE resolution_notes.transfer_to = 'admissions' AND resolution_notes.note_date > '$start' AND resolution_notes.note_date < '$end'");
			$transfers['admissions'] = mysql_result($query, 0, 0);
			
			$query = mysql_query("select count(*) from resolution_notes WHERE resolution_notes.transfer_to = 'counseling' AND resolution_notes.note_date > '$start' AND resolution_notes.note_date < '$end'");
			$transfers['counseling'] = mysql_result($query, 0, 0);
			
			$query = mysql_query("select count(*) from resolution_notes WHERE resolution_notes.transfer_to = 'admissions sup' AND resolution_notes.note_date > '$start' AND resolution_notes.note_date < '$end'");
			$transfers['admissionsSup'] = mysql_result($query, 0, 0);
			
			$query = mysql_query("select count(*) from resolution_notes WHERE resolution_notes.transfer_to = 'cashiers' AND resolution_notes.note_date > '$start' AND resolution_notes.note_date < '$end'");
			$transfers['cashiers'] = mysql_result($query, 0, 0);
			
			$query = mysql_query("select count(*) from resolution_notes WHERE resolution_notes.transfer_to = 'faculty staff HD' AND resolution_notes.note_date > '$start' AND resolution_notes.note_date < '$end'");
			$transfers['faculty'] = mysql_result($query, 0, 0);
			
			$query = mysql_query("select count(*) from resolution_notes WHERE resolution_notes.transfer_to = 'financial aid' AND resolution_notes.note_date > '$start' AND resolution_notes.note_date < '$end'");
			$transfers['financialAid'] = mysql_result($query, 0, 0);
			
			$query = mysql_query("select count(*) from resolution_notes WHERE resolution_notes.transfer_to = 'other' AND resolution_notes.note_date > '$start' AND resolution_notes.note_date < '$end'");
			$transfers['other'] = mysql_result($query, 0, 0);
			
			//this just adds all the transfers up, on one line for neatness
			foreach($transfers as $cur){ $totalTransfers = $totalTransfers + $cur; }
			
			foreach($transfers as $extension => $count)
			{
			?>
			<tr>
				<td class="description">
					<?php echo $extension; ?>
				</td>
				<td class="total">
					<?php echo $count; ?>
				</td>
				<td class="percent">
					<?php echo round(@($count / $totalTransfers) * 100, 1); echo "%";?>
				</td>
				<td>
					<?php echo round(@($count / $totalTickets) * 100, 1);echo "%";?>
				</td>
			</tr>
			<?php 
			}	//end of foreach (tricky huh. that one took me a while)
			?>
		</tbody>
		<tfoot>
			<tr>
				<td>
					<strong>Total transfers/referrals:</strong>
				</td>
				<td id="totalTransfers">
					<?php echo $totalTransfers; ?>
				</td>
				<td>100%</td>
				<td>
					<?php echo round(@($totalTransfers / $totalTickets) * 100, 1); ?>
				</td>
			</tr>
		</tfoot>
	</table>
	Note: No transfer data kept before 12-15-2010
</div>
		</td>
		<td>
			<div id="chart"></div>
		</td>
	</tr>
</table>
<script type="text/javascript">
function makeChart(tableId)
{
	//organize data
	var callData = new Array();
	
	var table = document.getElementById(tableId)
	var name = table.getElementsByClassName("description");
	var percent = table.getElementsByClassName("percent");
	for (var i = 0; i < name.length; i++)
	{
		callData[i] = new Array();
		callData[i][0] = name[i].innerHTML;
		callData[i][1] = parseInt(percent[i].innerHTML);
	}
	
	//see the mozilla documentation for Array.sort()
	//callData.sort(function(a, b){return b[1] - a[1]});
	//data is sorted in the table ahead of time

	//make pretty pixels
	var chart = new Highcharts.Chart({
	chart: {
		renderTo: 'chart',
		plotBackgroundColor: null,
		plotBorderWidth: null,
		plotShadow: false,
		height: 600
	},
	title: {
		text: 'Ticket problem types by percent'
	},
	tooltip: {
		formatter: function() {
			return '<b>'+ this.point.name +'</b>: '+ this.y +' %';
		}
	},
	plotOptions: {
		pie: {
			allowPointSelect: true,
			cursor: 'pointer',
			dataLabels: {
				enabled: false
			},
			showInLegend: true
		}
	},
	series: [{
		type: 'pie',
		name: 'problem types',
		data: callData
		}]
	});
}

window.onload =function(){
	sortTableByTotal("callResults", descending);
	sortTableByTotal("transferResults", descending);
	makeChart("callResults");
}
</script>
</body>
</html>
